﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Data.SqlClient;

namespace LibraryInterface
{
    class LibraryInterface
    {
        static void Main()
        {
            LibraryEntities lbEntity = new LibraryEntities();
            OperatingWithBooks(lbEntity);
            OperatingWithLibraryBooks(lbEntity);
            OperatingWithLibraries(lbEntity);
            OperatingWithListBooks(lbEntity);
            OperatingWithBorrowedBooks(lbEntity);
            OperatingWithSubscribers(lbEntity);
            OperatingWithPeople(lbEntity);

            int personID = 2;
            int libraryID = 1;
            TransactionUnsubscribing(personID, libraryID);

            StoredProcedureSubscribing(personID, libraryID);

            StoredProcedureUnSubscribing(personID, libraryID);
        }

        private static void StoredProcedureUnSubscribing(int personID, int libraryID)
        {
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; Database=Library; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmd = new SqlCommand("EXEC usp_UnsubscribePerson @libraryID,@personID", dbCon);
                cmd.Parameters.Add(new SqlParameter("@libraryID", libraryID));
                cmd.Parameters.Add(new SqlParameter("@personID", personID));
                cmd.ExecuteNonQuery();
            }
        }

        private static void StoredProcedureSubscribing(int personID, int libraryID)
        {
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; Database=Library; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmd = new SqlCommand("EXEC usp_SubscribePerson @libraryID,@personID", dbCon);
                cmd.Parameters.Add(new SqlParameter("@libraryID", libraryID));
                cmd.Parameters.Add(new SqlParameter("@personID", personID));
                cmd.ExecuteNonQuery();
            }
        }

        private static void TransactionUnsubscribing(int personID, int libraryID)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                try
                {
                    LibraryEntities lbEntity = new LibraryEntities();
                    LibrariesSubscriber subscription = lbEntity.LibrariesSubscribers.First<LibrariesSubscriber>(
                        s => s.PersonID == personID && s.LibraryID == libraryID);
                    int booksListID;
                    if (int.TryParse(subscription.ListID.ToString(), out booksListID))
                    {
                        if (lbEntity.BorrowedBooks.Any(b => b.ListID == booksListID))
                        {
                            throw new UnauthorizedAccessException();
                        }
                    }
                    else
                    {
                        lbEntity.LibrariesSubscribers.DeleteObject(subscription);
                        lbEntity.SaveChanges();
                    }
                    scope.Complete();
                }
                catch (UnauthorizedAccessException ex)
                {
                    Console.WriteLine("Can`t unsubscribe untill all books are returned!");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Invalid parameters.");
                }
            }
        }

        private static void OperatingWithPeople(LibraryEntities lbEntity)
        {
            string name = "Ivan";
            int age = 28;
            CreatePeople(name, age, lbEntity);

            int id = 1;
            ReadPeople(id, lbEntity);

            name = "Kaloian";
            UpdatePeople(id, name, age, lbEntity);

            id = 4;
            DeletePeople(id, lbEntity);
        }

        private static void DeletePeople(int id, LibraryEntities lbEntity)
        {
            Person searched = lbEntity.People.First<Person>(p => p.PersonID == id);
            lbEntity.People.DeleteObject(searched);
            lbEntity.SaveChanges();
        }

        private static void UpdatePeople(int id, string name, int age, LibraryEntities lbEntity)
        {
            Person searched = lbEntity.People.First<Person>(p => p.PersonID == id);
            searched.Name = name;
            searched.Age = age;
            lbEntity.SaveChanges();
        }

        private static void ReadPeople(int id, LibraryEntities lbEntity)
        {
            Person searched = lbEntity.People.First<Person>(p => p.PersonID == id);
            Console.WriteLine("Name: {0}\nAge: {1}", searched.Name, searched.Age);
        }

        private static void CreatePeople(string name, int age, LibraryEntities lbEntity)
        {
            Person person = new Person();
            person.Name = name;
            person.Age = age;
            lbEntity.People.AddObject(person);
            lbEntity.SaveChanges();
        }

        private static void OperatingWithSubscribers(LibraryEntities lbEntity)
        {
            int libraryID = 2;
            int personID = 3;
            int? listID = null;
            CreateSubscribers(libraryID, personID, listID, lbEntity);

            ReadSubscribers(libraryID, personID, lbEntity);

            int newLibraryID = 3;
            int newPersonID = 3;
            int? newListID = 1;
            UpdateSubscribers(libraryID, personID, newLibraryID, newPersonID, newListID, lbEntity);

            DeleteSubscribers(libraryID, personID, lbEntity);
        }

        private static void DeleteSubscribers(int libraryID, int personID, LibraryEntities lbEntity)
        {
            LibrariesSubscriber searchedSubscriber = lbEntity.LibrariesSubscribers.First<LibrariesSubscriber>(
                 s => (s.LibraryID == libraryID && s.PersonID == personID));
            lbEntity.LibrariesSubscribers.DeleteObject(searchedSubscriber);
            lbEntity.SaveChanges();
        }

        private static void UpdateSubscribers(
            int libraryID, int personID, int newLibraryID, int newPersonID, int? newListID, LibraryEntities lbEntity)
        {
            LibrariesSubscriber searchedSubscriber = lbEntity.LibrariesSubscribers.First<LibrariesSubscriber>(
                s => (s.LibraryID == libraryID && s.PersonID == personID));
            searchedSubscriber.LibraryID = newLibraryID;
            searchedSubscriber.PersonID = newPersonID;
            searchedSubscriber.ListID = newListID;
            lbEntity.LibrariesSubscribers.AddObject(searchedSubscriber);

            lbEntity.SaveChanges();
        }

        private static void ReadSubscribers(int libraryID, int personID, LibraryEntities lbEntity)
        {
            LibrariesSubscriber searchedSubscriber = lbEntity.LibrariesSubscribers.First<LibrariesSubscriber>(
                s => (s.LibraryID == libraryID && s.PersonID == personID));
            Console.WriteLine("Subscriber list ID: {0}", searchedSubscriber.ListID);
        }

        private static void CreateSubscribers(int libraryID, int personID, int? listID, LibraryEntities lbEntity)
        {
            LibrariesSubscriber subscriber = new LibrariesSubscriber();
            subscriber.LibraryID = libraryID;
            subscriber.PersonID = personID;
            subscriber.ListID = listID;
            lbEntity.LibrariesSubscribers.AddObject(subscriber);
            lbEntity.SaveChanges();
        }

        private static void OperatingWithBorrowedBooks(LibraryEntities lbEntity)
        {
            int bookID = 2;
            CreateBorrowedBook(bookID, lbEntity);

            int id = 1;
            ReadBorrowedBook(id, lbEntity);

            bookID = 1;
            UpdateBorrowedBook(id, bookID, lbEntity);

            id = 2;
            DeleteBorrowedBook(id, lbEntity);
        }

        private static void DeleteBorrowedBook(int id, LibraryEntities lbEntity)
        {
            BorrowedBook searched = lbEntity.BorrowedBooks.First<BorrowedBook>(b => b.ListID == id);
            lbEntity.BorrowedBooks.DeleteObject(searched);
            lbEntity.SaveChanges();
        }

        private static void UpdateBorrowedBook(int id, int bookID, LibraryEntities lbEntity)
        {
            BorrowedBook searched = lbEntity.BorrowedBooks.First<BorrowedBook>(b => b.ListID == id);
            searched.BookID = bookID;
            lbEntity.SaveChanges();
        }

        private static void ReadBorrowedBook(int id, LibraryEntities lbEntity)
        {
            BorrowedBook searched = lbEntity.BorrowedBooks.First<BorrowedBook>(b => b.ListID == id);
            Console.WriteLine("Id: {0}", searched.BookID);
        }

        private static void CreateBorrowedBook(int bookID, LibraryEntities lbEntity)
        {
            BorrowedBook borrowedBook = new BorrowedBook();
            borrowedBook.BookID = bookID;
            lbEntity.BorrowedBooks.AddObject(borrowedBook);
            lbEntity.SaveChanges();
        }

        private static void OperatingWithListBooks(LibraryEntities lbEntity)
        {
            int listID = 2;
            int bookID = 3;
            CreateListBooks(listID, bookID, lbEntity);

            ReadListBooks(listID, bookID, lbEntity);

            int newListID = 3;
            int newBookID = 3;
            UpdateListBooks(listID, bookID, newListID, newBookID, lbEntity);

            DeleteListBooks(listID, bookID, lbEntity);
        }

        private static void DeleteListBooks(int listID, int bookID, LibraryEntities lbEntity)
        {
            Book searchedBook = lbEntity.Books.First<Book>(b => b.BookID == bookID);
            BorrowedBook searchedList = lbEntity.BorrowedBooks.First<BorrowedBook>(b => b.ListID == listID);
            searchedList.Books.Remove(searchedBook);
            lbEntity.SaveChanges();
        }

        private static void UpdateListBooks(int listID, int bookID, int newListID, int newBookID, LibraryEntities lbEntity)
        {
            Book searchedBook = lbEntity.Books.First<Book>(b => b.BookID == bookID);
            BorrowedBook searchedList = lbEntity.BorrowedBooks.First<BorrowedBook>(b => b.ListID == listID);
            searchedList.Books.Remove(searchedBook);

            searchedList = lbEntity.BorrowedBooks.First<BorrowedBook>(b => b.ListID == newListID);
            searchedBook = lbEntity.Books.First<Book>(b => b.BookID == newBookID);
            searchedList.Books.Add(searchedBook);

            lbEntity.SaveChanges();
        }

        private static void ReadListBooks(int listID, int bookID, LibraryEntities lbEntity)
        {
            BorrowedBook searchedList = lbEntity.BorrowedBooks.First<BorrowedBook>(b => b.ListID == listID);
            Book searchedBook = lbEntity.Books.First<Book>(b => b.BookID == bookID);
            Console.WriteLine("Library Name: {0}", searchedList.BookID);
            Console.WriteLine("Book Name: {0}\nISBN: {1}", searchedBook.Name, searchedBook.ISBN);
        }

        private static void CreateListBooks(int listID, int bookID, LibraryEntities lbEntity)
        {
            BorrowedBook searchedList = lbEntity.BorrowedBooks.First<BorrowedBook>(b => b.ListID == listID);
            Book searchedBook = lbEntity.Books.First<Book>(b => b.BookID == bookID);
            searchedList.Books.Add(searchedBook);
            lbEntity.SaveChanges();
        }

        private static void OperatingWithLibraries(LibraryEntities lbEntity)
        {
            string name = "Kvartalna";
            int maxBooks = 27;
            CreateLibrary(name, maxBooks, lbEntity);

            int id = 1;
            ReadLibrary(id, lbEntity);

            name = "Raionna";
            maxBooks = 54;
            UpdateLibrary(id, name, maxBooks, lbEntity);

            id = 2;
            DeleteLibrary(id, lbEntity);
        }

        private static void DeleteLibrary(int id, LibraryEntities lbEntity)
        {
            Library searched = lbEntity.Libraries.First<Library>(l => l.LibraryID == id);
            lbEntity.Libraries.DeleteObject(searched);
            lbEntity.SaveChanges();
        }

        private static void UpdateLibrary(int id, string name, int maxBooks, LibraryEntities lbEntity)
        {
            Library searched = lbEntity.Libraries.First<Library>(l => l.LibraryID == id);
            searched.Name = name;
            searched.MaxBooks = maxBooks;
            lbEntity.SaveChanges();
        }

        private static void ReadLibrary(int id, LibraryEntities lbEntity)
        {
            Library searched = lbEntity.Libraries.First<Library>(l => l.LibraryID == id);
            Console.WriteLine("Name: {0}", searched.Name);
        }

        private static void CreateLibrary(string name, int maxBooks, LibraryEntities lbEntity)
        {
            Library library = new Library();
            library.Name = name;
            library.MaxBooks = maxBooks;
            lbEntity.Libraries.AddObject(library);
            lbEntity.SaveChanges();
        }

        private static void OperatingWithLibraryBooks(LibraryEntities lbEntity)
        {
            int libraryID = 2;
            int bookID = 3;
            CreateLibraryBook(libraryID, bookID, lbEntity);

            ReadLibraryBook(libraryID, bookID, lbEntity);

            int newLibraryID = 3;
            int newBookID = 3;
            UpdateLibraryBook(libraryID, bookID, newLibraryID, newBookID, lbEntity);

            DeleteLibraryBook(libraryID, bookID, lbEntity);
        }

        private static void DeleteLibraryBook(int libraryID, int bookID, LibraryEntities lbEntity)
        {
            Book searchedBook = lbEntity.Books.First<Book>(b => b.BookID == bookID);
            Library searchedLibrary = lbEntity.Libraries.First<Library>(l => l.LibraryID == libraryID);
            searchedLibrary.Books.Remove(searchedBook);
            lbEntity.SaveChanges();
        }

        private static void UpdateLibraryBook(int libraryID, int bookID, int newLibraryID, int newBookID, LibraryEntities lbEntity)
        {
            Book searchedBook = lbEntity.Books.First<Book>(b => b.BookID == bookID);
            Library searchedLibrary = lbEntity.Libraries.First<Library>(l => l.LibraryID == libraryID);
            searchedLibrary.Books.Remove(searchedBook);

            searchedLibrary = lbEntity.Libraries.First<Library>(l => l.LibraryID == newLibraryID);
            searchedBook = lbEntity.Books.First<Book>(b => b.BookID == newBookID);
            searchedLibrary.Books.Add(searchedBook);

            lbEntity.SaveChanges();
        }

        private static void ReadLibraryBook(int libraryID, int bookID, LibraryEntities lbEntity)
        {
            Library searchedLibrary = lbEntity.Libraries.First<Library>(l => l.LibraryID == libraryID);
            Book searchedBook = lbEntity.Books.First<Book>(b => b.BookID == bookID);
            Console.WriteLine("Library Name: {0}", searchedLibrary.Name);
            Console.WriteLine("Book Name: {0}\nISBN: {1}", searchedBook.Name, searchedBook.ISBN);
        }

        private static void CreateLibraryBook(int libraryID, int bookID, LibraryEntities lbEntity)
        {
            Library searchedLibrary = lbEntity.Libraries.First<Library>(l => l.LibraryID == libraryID);
            Book searchedBook = lbEntity.Books.First<Book>(b => b.BookID == bookID);
            searchedLibrary.Books.Add(searchedBook);
            lbEntity.SaveChanges();
        }

        private static void OperatingWithBooks(LibraryEntities lbEntity)
        {
            string name = "Pod Igoto";
            int isbn = 1954367;
            string type = "fiction book";
            CreateBook(name, isbn, type, lbEntity);

            int id = 1;
            ReadBook(id, lbEntity);

            name = "Heroes of Unknown";
            type = "textbook";
            UpdateBook(id, name, isbn, type, lbEntity);

            id = 4;
            DeleteBook(id, lbEntity);
        }

        private static void DeleteBook(int id, LibraryEntities lbEntity)
        {
            Book searched = lbEntity.Books.First<Book>(b => b.BookID == id);
            lbEntity.Books.DeleteObject(searched);
            lbEntity.SaveChanges();
        }

        private static void UpdateBook(int id, string name, int isbn, string type, LibraryEntities lbEntity)
        {
            Book searched = lbEntity.Books.First<Book>(b => b.BookID == id);
            searched.Name = name;
            searched.ISBN = isbn;
            searched.Type = type;
            lbEntity.SaveChanges();
        }

        private static void ReadBook(int id, LibraryEntities lbEntity)
        {
            Book searched = lbEntity.Books.First<Book>(b => b.BookID == id);
            Console.WriteLine("Name: {0}\nISBN: {1}", searched.Name, searched.ISBN);
        }

        private static void CreateBook(string name, int isbn, string type, LibraryEntities lbEntity)
        {
            Book book = new Book();
            book.Name=name;
            book.ISBN=isbn;
            book.Type = type;
            lbEntity.Books.AddObject(book);
            lbEntity.SaveChanges();
        }
    }
}
